package database

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"time"

	"workflow-gui/internal/workflow"
)

// ProjectDAO handles project-related database operations
type ProjectDAO struct {
	db *DB
}

// NewProjectDAO creates a new ProjectDAO
func NewProjectDAO(db *DB) *ProjectDAO {
	return &ProjectDAO{db: db}
}

// ProjectListItem represents a project in the list view
type ProjectListItem struct {
	ID              int       `json:"id"`
	ChangeID        string    `json:"change_id"`
	Name            string    `json:"name"`
	Status          string    `json:"status"`
	WorkflowStage   string    `json:"workflow_stage"`
	CreatedAt       time.Time `json:"created_at"`
	UpdatedAt       time.Time `json:"updated_at"`
	Complexity      string    `json:"complexity"`
	TotalTasks      int       `json:"total_tasks"`
	CompletedTasks  int       `json:"completed_tasks"`
	InProgressTasks int       `json:"in_progress_tasks"`
	EvaluationScore int       `json:"evaluation_score"`
}

// List returns all projects
func (dao *ProjectDAO) List() ([]ProjectListItem, error) {
	query := `SELECT * FROM v_project_list ORDER BY updated_at DESC`

	rows, err := dao.db.Query(query)
	if err != nil {
		return nil, fmt.Errorf("failed to query projects: %w", err)
	}
	defer rows.Close()

	var projects []ProjectListItem
	for rows.Next() {
		var p ProjectListItem
		var complexity sql.NullString
		var evalScore sql.NullInt64

		err := rows.Scan(
			&p.ID, &p.ChangeID, &p.Name, &p.Status, &p.WorkflowStage,
			&p.CreatedAt, &p.UpdatedAt, &complexity,
			&p.TotalTasks, &p.CompletedTasks, &p.InProgressTasks,
			&evalScore,
		)
		if err != nil {
			return nil, fmt.Errorf("failed to scan project: %w", err)
		}

		if complexity.Valid {
			p.Complexity = complexity.String
		}
		if evalScore.Valid {
			p.EvaluationScore = int(evalScore.Int64)
		}

		projects = append(projects, p)
	}

	return projects, rows.Err()
}

// GetByChangeID retrieves a project by change_id
func (dao *ProjectDAO) GetByChangeID(changeID string) (*workflow.WorkflowData, error) {
	fmt.Printf("DEBUG: GetByChangeID called with changeID=%q\n", changeID)

	// Query project
	var projectID int
	var meta workflow.Meta
	var schemaVersion string

	query := `SELECT id, change_id, created_at, updated_at, status, workflow_stage, session_id, round, complexity, schema_version
	          FROM projects WHERE change_id = ?`

	fmt.Printf("DEBUG: Executing query: %s with param: %q\n", query, changeID)

	var complexity sql.NullString
	err := dao.db.QueryRow(query, changeID).Scan(
		&projectID, &meta.ChangeID, &meta.CreatedAt, &meta.UpdatedAt,
		&meta.Status, &meta.WorkflowStage, &meta.SessionID, &meta.Round, &complexity, &schemaVersion,
	)
	if err != nil {
		fmt.Printf("DEBUG: Query error: %v\n", err)
		return nil, fmt.Errorf("failed to get project: %w", err)
	}
	fmt.Printf("DEBUG: Query successful, projectID=%d, change_id=%s\n", projectID, meta.ChangeID)
	if complexity.Valid {
		meta.Complexity = complexity.String
	}

	// Build complete workflow data
	workflow := &workflow.WorkflowData{
		Meta:          meta,
		SchemaVersion: schemaVersion,
	}

	// Load proposal
	fmt.Printf("DEBUG: Loading proposal for projectID=%d\n", projectID)
	if err := dao.loadProposal(projectID, workflow); err != nil {
		fmt.Printf("DEBUG: loadProposal error: %v\n", err)
		return nil, err
	}

	// Load requirements & scenarios
	fmt.Printf("DEBUG: Loading requirements for projectID=%d\n", projectID)
	if err := dao.loadRequirements(projectID, workflow); err != nil {
		fmt.Printf("DEBUG: loadRequirements error: %v\n", err)
		return nil, err
	}

	// Load tasks (with deliverables, verification, execution)
	fmt.Printf("DEBUG: Loading tasks for projectID=%d\n", projectID)
	if err := dao.loadTasks(projectID, workflow); err != nil {
		fmt.Printf("DEBUG: loadTasks error: %v\n", err)
		return nil, err
	}

	// Load evaluation
	fmt.Printf("DEBUG: Loading evaluation for projectID=%d\n", projectID)
	if err := dao.loadEvaluation(projectID, workflow); err != nil {
		fmt.Printf("DEBUG: loadEvaluation error: %v\n", err)
		return nil, err
	}

	// Load history
	fmt.Printf("DEBUG: Loading history for projectID=%d\n", projectID)
	if err := dao.loadHistory(projectID, workflow); err != nil {
		fmt.Printf("DEBUG: loadHistory error: %v\n", err)
		return nil, err
	}

	return workflow, nil
}

// Create creates a new project
func (dao *ProjectDAO) Create(wf *workflow.WorkflowData) error {
	tx, err := dao.db.Begin()
	if err != nil {
		return fmt.Errorf("failed to begin transaction: %w", err)
	}
	defer tx.Rollback()

	// Insert project
	result, err := tx.Exec(`
		INSERT INTO projects (change_id, created_at, updated_at, status, workflow_stage, session_id, round, complexity, schema_version)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		wf.Meta.ChangeID, wf.Meta.CreatedAt, wf.Meta.UpdatedAt, wf.Meta.Status, wf.Meta.WorkflowStage,
		wf.Meta.SessionID, wf.Meta.Round, wf.Meta.Complexity, "2.0",
	)
	if err != nil {
		return fmt.Errorf("failed to insert project: %w", err)
	}

	projectID, err := result.LastInsertId()
	if err != nil {
		return fmt.Errorf("failed to get project ID: %w", err)
	}

	// Insert proposal
	scopeJSON, _ := json.Marshal(wf.Proposal.Scope)
	affectedFilesJSON, _ := json.Marshal(wf.Proposal.Scope.AffectedFiles)
	_, err = tx.Exec(`
		INSERT INTO proposals (project_id, summary, motivation, scope_json, change_type, priority, breaking_changes, affected_files_json)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
		projectID, wf.Proposal.Summary, wf.Proposal.Motivation, scopeJSON,
		wf.Proposal.Scope.ChangeType, wf.Proposal.Scope.Priority, wf.Proposal.Scope.BreakingChanges,
		affectedFilesJSON,
	)
	if err != nil {
		return fmt.Errorf("failed to insert proposal: %w", err)
	}

	// Insert evaluation if exists
	if wf.Evaluation.TotalScore > 0 {
		missingInfoJSON, _ := json.Marshal(wf.Evaluation.MissingInfo)
		_, err = tx.Exec(`
			INSERT INTO evaluations (project_id, total_score, confidence_level, missing_info_json, reasoning)
			VALUES (?, ?, ?, ?, ?)`,
			projectID, wf.Evaluation.TotalScore, wf.Evaluation.ConfidenceLevel,
			missingInfoJSON, wf.Evaluation.Reasoning,
		)
		if err != nil {
			return fmt.Errorf("failed to insert evaluation: %w", err)
		}
	}

	return tx.Commit()
}

// Update updates an existing project
func (dao *ProjectDAO) Update(changeID string, wf *workflow.WorkflowData) error {
	tx, err := dao.db.Begin()
	if err != nil {
		return fmt.Errorf("failed to begin transaction: %w", err)
	}
	defer tx.Rollback()

	// Update project
	_, err = tx.Exec(`
		UPDATE projects
		SET updated_at = ?, status = ?, workflow_stage = ?, round = ?, complexity = ?
		WHERE change_id = ?`,
		wf.Meta.UpdatedAt, wf.Meta.Status, wf.Meta.WorkflowStage, wf.Meta.Round, wf.Meta.Complexity, changeID,
	)
	if err != nil {
		return fmt.Errorf("failed to update project: %w", err)
	}

	// Get project ID
	var projectID int
	err = tx.QueryRow("SELECT id FROM projects WHERE change_id = ?", changeID).Scan(&projectID)
	if err != nil {
		return fmt.Errorf("failed to get project ID: %w", err)
	}

	// Update proposal
	scopeJSON, _ := json.Marshal(wf.Proposal.Scope)
	affectedFilesJSON, _ := json.Marshal(wf.Proposal.Scope.AffectedFiles)
	_, err = tx.Exec(`
		UPDATE proposals
		SET summary = ?, motivation = ?, scope_json = ?, change_type = ?, priority = ?, breaking_changes = ?, affected_files_json = ?
		WHERE project_id = ?`,
		wf.Proposal.Summary, wf.Proposal.Motivation, scopeJSON,
		wf.Proposal.Scope.ChangeType, wf.Proposal.Scope.Priority, wf.Proposal.Scope.BreakingChanges,
		affectedFilesJSON, projectID,
	)
	if err != nil {
		return fmt.Errorf("failed to update proposal: %w", err)
	}

	return tx.Commit()
}

// Delete deletes a project
func (dao *ProjectDAO) Delete(changeID string) error {
	result, err := dao.db.Exec("DELETE FROM projects WHERE change_id = ?", changeID)
	if err != nil {
		return fmt.Errorf("failed to delete project: %w", err)
	}

	rows, err := result.RowsAffected()
	if err != nil {
		return fmt.Errorf("failed to get affected rows: %w", err)
	}

	if rows == 0 {
		return fmt.Errorf("project not found")
	}

	return nil
}

// loadProposal loads proposal data
func (dao *ProjectDAO) loadProposal(projectID int, wf *workflow.WorkflowData) error {
	query := `SELECT summary, motivation, scope_json, affected_files_json FROM proposals WHERE project_id = ?`

	var scopeJSON, affectedFilesJSON string
	err := dao.db.QueryRow(query, projectID).Scan(
		&wf.Proposal.Summary,
		&wf.Proposal.Motivation,
		&scopeJSON,
		&affectedFilesJSON,
	)
	if err != nil {
		return fmt.Errorf("failed to load proposal: %w", err)
	}

	// Parse scope JSON
	if err := json.Unmarshal([]byte(scopeJSON), &wf.Proposal.Scope); err != nil {
		return fmt.Errorf("failed to parse scope JSON: %w", err)
	}

	// Parse affected files JSON
	if affectedFilesJSON != "" && affectedFilesJSON != "[]" {
		if err := json.Unmarshal([]byte(affectedFilesJSON), &wf.Proposal.Scope.AffectedFiles); err != nil {
			return fmt.Errorf("failed to parse affected files JSON: %w", err)
		}
	}

	return nil
}

// loadRequirements loads requirements and scenarios
func (dao *ProjectDAO) loadRequirements(projectID int, wf *workflow.WorkflowData) error {
	query := `SELECT id, req_id, operation, name, description FROM requirements WHERE project_id = ?`
	rows, err := dao.db.Query(query, projectID)
	if err != nil {
		return fmt.Errorf("failed to query requirements: %w", err)
	}
	defer rows.Close()

	var requirements []workflow.Requirement
	for rows.Next() {
		var req workflow.Requirement
		var reqDBID int

		err := rows.Scan(&reqDBID, &req.ID, &req.Operation, &req.Name, &req.Description)
		if err != nil {
			return fmt.Errorf("failed to scan requirement: %w", err)
		}

		// Load scenarios for this requirement
		scenarios, err := dao.loadScenarios(reqDBID)
		if err != nil {
			return err
		}
		req.Scenarios = scenarios

		requirements = append(requirements, req)
	}

	wf.Spec.Requirements = requirements
	return rows.Err()
}

// loadScenarios loads scenarios for a requirement
func (dao *ProjectDAO) loadScenarios(requirementID int) ([]workflow.Scenario, error) {
	query := `SELECT scenario_id, name, when_condition, then_result FROM scenarios WHERE requirement_id = ?`
	rows, err := dao.db.Query(query, requirementID)
	if err != nil {
		return nil, fmt.Errorf("failed to query scenarios: %w", err)
	}
	defer rows.Close()

	var scenarios []workflow.Scenario
	for rows.Next() {
		var s workflow.Scenario
		err := rows.Scan(&s.ID, &s.Name, &s.When, &s.Then)
		if err != nil {
			return nil, fmt.Errorf("failed to scan scenario: %w", err)
		}
		scenarios = append(scenarios, s)
	}

	return scenarios, rows.Err()
}

// loadTasks loads tasks with all related data
func (dao *ProjectDAO) loadTasks(projectID int, wf *workflow.WorkflowData) error {
	query := `SELECT id, task_id, number, name, description, status FROM tasks WHERE project_id = ? ORDER BY display_order`
	rows, err := dao.db.Query(query, projectID)
	if err != nil {
		return fmt.Errorf("failed to query tasks: %w", err)
	}
	defer rows.Close()

	var tasks []workflow.Task
	taskCount := 0
	completedCount := 0

	for rows.Next() {
		var task workflow.Task
		var taskDBID int

		err := rows.Scan(&taskDBID, &task.ID, &task.Number, &task.Name, &task.Description, &task.Status)
		if err != nil {
			return fmt.Errorf("failed to scan task: %w", err)
		}

		taskCount++
		if task.Status == "completed" {
			completedCount++
		}

		// Load dependencies
		deps, err := dao.loadTaskDependencies(taskDBID)
		if err != nil {
			return err
		}
		task.Dependencies = deps

		// Load deliverables
		deliverables, err := dao.loadDeliverables(taskDBID)
		if err != nil {
			return err
		}
		task.Deliverables = deliverables

		// Load verification
		verification, err := dao.loadVerification(taskDBID)
		if err != nil {
			return err
		}
		task.Verification = *verification

		// Load execution
		execution, err := dao.loadExecution(taskDBID)
		if err != nil {
			return err
		}
		task.Execution = *execution

		tasks = append(tasks, task)
	}

	wf.Tasks.Items = tasks
	wf.Tasks.Total = taskCount
	wf.Tasks.Completed = completedCount

	return rows.Err()
}

// loadTaskDependencies loads dependencies for a task
func (dao *ProjectDAO) loadTaskDependencies(taskDBID int) ([]string, error) {
	query := `
		SELECT t.task_id
		FROM task_dependencies td
		JOIN tasks t ON t.id = td.depends_on_task_id
		WHERE td.task_id = ?
	`
	rows, err := dao.db.Query(query, taskDBID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var deps []string
	for rows.Next() {
		var depID string
		if err := rows.Scan(&depID); err != nil {
			return nil, err
		}
		deps = append(deps, depID)
	}

	return deps, rows.Err()
}

// loadDeliverables loads deliverables for a task
func (dao *ProjectDAO) loadDeliverables(taskDBID int) ([]workflow.Deliverable, error) {
	query := `SELECT type, path, description, acceptance_criteria_json FROM deliverables WHERE task_id = ?`
	rows, err := dao.db.Query(query, taskDBID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var deliverables []workflow.Deliverable
	for rows.Next() {
		var d workflow.Deliverable
		var criteriaJSON string

		err := rows.Scan(&d.Type, &d.Path, &d.Description, &criteriaJSON)
		if err != nil {
			return nil, err
		}

		if err := json.Unmarshal([]byte(criteriaJSON), &d.AcceptanceCriteria); err != nil {
			return nil, fmt.Errorf("failed to parse acceptance criteria: %w", err)
		}

		deliverables = append(deliverables, d)
	}

	return deliverables, rows.Err()
}

// loadVerification loads verification for a task
func (dao *ProjectDAO) loadVerification(taskDBID int) (*workflow.Verification, error) {
	query := `SELECT type, scenario_refs_json, manual_check_json FROM verifications WHERE task_id = ?`

	var v workflow.Verification
	var scenarioRefsJSON, manualCheckJSON string

	err := dao.db.QueryRow(query, taskDBID).Scan(&v.Type, &scenarioRefsJSON, &manualCheckJSON)
	if err != nil {
		return nil, err
	}

	if err := json.Unmarshal([]byte(scenarioRefsJSON), &v.ScenarioRefs); err != nil {
		return nil, fmt.Errorf("failed to parse scenario refs: %w", err)
	}

	if err := json.Unmarshal([]byte(manualCheckJSON), &v.ManualCheck); err != nil {
		return nil, fmt.Errorf("failed to parse manual check: %w", err)
	}

	return &v, nil
}

// loadExecution loads execution info for a task
func (dao *ProjectDAO) loadExecution(taskDBID int) (*workflow.Execution, error) {
	query := `SELECT id, status, started_at, completed_at, ai_model, prompt_used, result FROM executions WHERE task_id = ?`

	var e workflow.Execution
	var execDBID int
	var startedAt, completedAt sql.NullTime
	var aiModel, promptUsed, result sql.NullString

	err := dao.db.QueryRow(query, taskDBID).Scan(
		&execDBID, &e.Status, &startedAt, &completedAt, &aiModel, &promptUsed, &result,
	)
	if err != nil {
		return nil, err
	}

	if startedAt.Valid {
		e.StartedAt = &startedAt.Time
	}
	if completedAt.Valid {
		e.CompletedAt = &completedAt.Time
	}
	if aiModel.Valid {
		e.AIModel = aiModel.String
	}
	if promptUsed.Valid {
		e.PromptUsed = promptUsed.String
	}
	if result.Valid {
		e.Result = result.String
	}

	// Load execution history
	history, err := dao.loadExecutionHistory(execDBID)
	if err != nil {
		return nil, err
	}
	e.History = history

	return &e, nil
}

// loadExecutionHistory loads execution history
func (dao *ProjectDAO) loadExecutionHistory(execDBID int) ([]workflow.ExecutionHistory, error) {
	query := `SELECT timestamp, event, from_status, to_status, details FROM execution_history WHERE execution_id = ? ORDER BY timestamp`
	rows, err := dao.db.Query(query, execDBID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var history []workflow.ExecutionHistory
	for rows.Next() {
		var h workflow.ExecutionHistory
		var fromStatus, toStatus sql.NullString

		err := rows.Scan(&h.Timestamp, &h.Event, &fromStatus, &toStatus, &h.Details)
		if err != nil {
			return nil, err
		}

		if fromStatus.Valid {
			h.From = fromStatus.String
		}
		if toStatus.Valid {
			h.To = toStatus.String
		}

		history = append(history, h)
	}

	return history, rows.Err()
}

// loadEvaluation loads evaluation data
func (dao *ProjectDAO) loadEvaluation(projectID int, wf *workflow.WorkflowData) error {
	query := `
		SELECT total_score, background_understanding, background_reasoning, background_unclear_json,
		       technical_understanding, technical_reasoning, technical_unclear_json,
		       implementation_understanding, implementation_reasoning, implementation_unclear_json,
		       completeness, confidence_level, missing_info_json, contradictions_json, reasoning
		FROM evaluations WHERE project_id = ?
	`

	var e workflow.Evaluation
	var backgroundUnclearJSON, technicalUnclearJSON, implementationUnclearJSON, missingInfoJSON, contradictionsJSON string
	var backgroundReasoning, technicalReasoning, implementationReasoning sql.NullString

	err := dao.db.QueryRow(query, projectID).Scan(
		&e.TotalScore,
		&e.BackgroundUnderstanding, &backgroundReasoning, &backgroundUnclearJSON,
		&e.TechnicalUnderstanding, &technicalReasoning, &technicalUnclearJSON,
		&e.ImplementationUnderstanding, &implementationReasoning, &implementationUnclearJSON,
		&e.Completeness, &e.ConfidenceLevel, &missingInfoJSON, &contradictionsJSON, &e.Reasoning,
	)
	if err != nil {
		// If no evaluation exists, that's OK - just use default values
		if err == sql.ErrNoRows {
			wf.Evaluation = workflow.Evaluation{}
			return nil
		}
		return err
	}

	if backgroundReasoning.Valid {
		e.BackgroundReasoning = backgroundReasoning.String
	}
	if technicalReasoning.Valid {
		e.TechnicalReasoning = technicalReasoning.String
	}
	if implementationReasoning.Valid {
		e.ImplementationReasoning = implementationReasoning.String
	}

	json.Unmarshal([]byte(backgroundUnclearJSON), &e.BackgroundUnclear)
	json.Unmarshal([]byte(technicalUnclearJSON), &e.TechnicalUnclear)
	json.Unmarshal([]byte(implementationUnclearJSON), &e.ImplementationUnclear)
	json.Unmarshal([]byte(missingInfoJSON), &e.MissingInfo)
	json.Unmarshal([]byte(contradictionsJSON), &e.Contradictions)

	wf.Evaluation = e
	return nil
}

// loadHistory loads project history
func (dao *ProjectDAO) loadHistory(projectID int, wf *workflow.WorkflowData) error {
	query := `SELECT timestamp, event, details FROM project_history WHERE project_id = ? ORDER BY timestamp`
	rows, err := dao.db.Query(query, projectID)
	if err != nil {
		return err
	}
	defer rows.Close()

	var history []workflow.History
	for rows.Next() {
		var h workflow.History
		err := rows.Scan(&h.Timestamp, &h.Event, &h.Details)
		if err != nil {
			return err
		}
		history = append(history, h)
	}

	wf.History = history
	return rows.Err()
}
